//////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////
//
// copyright            : (C) 2014 Eran Ifrah
// file name            : MySqlDbAdapter.cpp
//
// -------------------------------------------------------------------------
// A
//              _____           _      _     _ _
//             /  __ \         | |    | |   (_) |
//             | /  \/ ___   __| | ___| |    _| |_ ___
//             | |    / _ \ / _  |/ _ \ |   | | __/ _ )
//             | \__/\ (_) | (_| |  __/ |___| | ||  __/
//              \____/\___/ \__,_|\___\_____/_|\__\___|
//
//                                                  F i l e
//
//    This program is free software; you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation; either version 2 of the License, or
//    (at your option) any later version.
//
//////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////

#include "MySqlDbAdapter.h"

#include "StdToWX.h"
#include "constraint.h"
#include "database.h"
#include "dbconnection.h"
#include "table.h"
#include "view.h"

#include <wx/regex.h>

MySqlDbAdapter::MySqlDbAdapter()
{
    this->m_serverName = wxT("");
    this->m_userName = wxT("");
    this->m_password = wxT("");
    this->m_adapterType = atMYSQL;
    this->m_pDbLayer = NULL;
}
MySqlDbAdapter::MySqlDbAdapter(const wxString& serverName, const wxString& userName, const wxString& password)
{
    this->m_serverName = serverName;
    this->m_userName = userName;
    this->m_password = password;
    this->m_adapterType = atMYSQL;
    this->m_pDbLayer = NULL;
}

MySqlDbAdapter::~MySqlDbAdapter() {}

void MySqlDbAdapter::CloseConnection()
{
    if(this->m_pDbLayer)
        this->m_pDbLayer->Close();
}

DatabaseLayerPtr MySqlDbAdapter::GetDatabaseLayer(const wxString& dbName)
{
    DatabaseLayerPtr dbLayer;

#ifdef DBL_USE_MYSQL
    if(!CanConnect())
        return std::make_shared<MysqlDatabaseLayer>();
    dbLayer = std::make_shared<MysqlDatabaseLayer>(this->m_serverName, wxT(""), this->m_userName, this->m_password);
#endif
    this->m_pDbLayer = dbLayer;

    return dbLayer;
}

bool MySqlDbAdapter::IsConnected()
{
    if(this->m_pDbLayer)
        return this->m_pDbLayer->IsOpen();
    else
        return false;
}

wxString MySqlDbAdapter::GetCreateTableSql(Table* tab, bool dropTable)
{
    // TODO:SQL:
    wxString str = wxT("");
    if(dropTable)
        str = wxString::Format(
            wxT("SET FOREIGN_KEY_CHECKS = 0;\nDROP TABLE IF EXISTS `%s` ;\nSET FOREIGN_KEY_CHECKS = 1; \n"),
            tab->GetName().c_str());
    str.append(wxString::Format(wxT("CREATE TABLE `%s` (\n"), tab->GetName().c_str()));

    SerializableList::compatibility_iterator node = tab->GetFirstChildNode();
    while(node) {
        Column* col = NULL;
        if(node->GetData()->IsKindOf(CLASSINFO(Column)))
            col = (Column*)node->GetData();
        if(col)
            str.append(wxString::Format(wxT("\t`%s` %s"), col->GetName().c_str(), col->GetType()->ReturnSql().c_str()));

        Constraint* constr = wxDynamicCast(node->GetData(), Constraint);
        if(constr) {
            if(constr->GetType() == Constraint::primaryKey)
                str.append(wxString::Format(wxT("\tPRIMARY KEY (`%s`) \n"), constr->GetLocalColumn().c_str()));
        }

        node = node->GetNext();
        if(node) {
            if(wxDynamicCast(node->GetData(), Column))
                str.append(wxT(",\n "));
            else if((constr = wxDynamicCast(node->GetData(), Constraint))) {
                if(constr->GetType() == Constraint::primaryKey)
                    str.append(wxT(",\n "));
            }
        }
        // else  str.append(wxT("\n ")) ;
    }

    /*	Column* col = tab->GetFristColumn();
        while (col) {
            str.append(wxString::Format(wxT("\t`%s` %s"),col->getName().c_str(), col->getPType()->ReturnSql().c_str()));
            col = wxDynamicCast(col->GetSibbling(),Column);
            if (col) str.append(wxT(",\n ")) ;
            else  str.append(wxT("\n ")) ;
        }*/

    str.append(wxT("\n) ENGINE=INNODB;\n"));
    str.append(wxT("-- -------------------------------------------------------------\n"));
    return str;
}

IDbType* MySqlDbAdapter::GetDbTypeByName(const wxString& typeName)
{
    IDbType* type = NULL;
    if(typeName == wxT("INT")) {
        type = new MySqlType(wxT("INT"), IDbType::dbtAUTO_INCREMENT | IDbType::dbtNOT_NULL | IDbType::dbtSIZE,
                             IDbType::dbtTYPE_INT);
    } else if(typeName == wxT("VARCHAR")) {
        type = new MySqlType(wxT("VARCHAR"), IDbType::dbtUNIQUE | IDbType::dbtNOT_NULL | IDbType::dbtSIZE,
                             IDbType::dbtTYPE_TEXT);
    } else if(typeName == wxT("DOUBLE")) {
        type = new MySqlType(wxT("DOUBLE"), IDbType::dbtAUTO_INCREMENT | IDbType::dbtNOT_NULL | IDbType::dbtSIZE,
                             IDbType::dbtTYPE_DECIMAL);
    } else if(typeName == wxT("FLOAT")) {
        type = new MySqlType(wxT("FLOAT"), IDbType::dbtUNIQUE | IDbType::dbtNOT_NULL, IDbType::dbtTYPE_FLOAT);
    } else if(typeName == wxT("DECIMAL")) {
        type = new MySqlType(wxT("DECIMAL"),
                             IDbType::dbtUNIQUE | IDbType::dbtNOT_NULL | IDbType::dbtSIZE | IDbType::dbtSIZE_TWO,
                             IDbType::dbtTYPE_DECIMAL);
    } else if(typeName == wxT("BOOL")) {
        type = new MySqlType(wxT("BOOL"), 0, IDbType::dbtTYPE_BOOLEAN);
    } else if(typeName == wxT("DATETIME")) {
        type = new MySqlType(wxT("DATETIME"), IDbType::dbtUNIQUE | IDbType::dbtNOT_NULL, IDbType::dbtTYPE_DATE_TIME);
    } else if(typeName == wxT("TINYINT")) {
        type = new MySqlType(wxT("TINYINT"), IDbType::dbtAUTO_INCREMENT | IDbType::dbtNOT_NULL | IDbType::dbtSIZE,
                             IDbType::dbtTYPE_INT);
    } else if(typeName == wxT("BIGINT")) {
        type = new MySqlType(wxT("BIGINT"), IDbType::dbtAUTO_INCREMENT | IDbType::dbtNOT_NULL | IDbType::dbtSIZE,
                             IDbType::dbtTYPE_INT);
    } else if(typeName == wxT("SMALLINT")) {
        type = new MySqlType(wxT("SMALLINT"), IDbType::dbtAUTO_INCREMENT | IDbType::dbtNOT_NULL | IDbType::dbtSIZE,
                             IDbType::dbtTYPE_INT);
    } else if(typeName == wxT("CHAR")) {
        type = new MySqlType(wxT("CHAR"), IDbType::dbtNOT_NULL | IDbType::dbtSIZE, IDbType::dbtTYPE_TEXT);
    } else if(typeName == wxT("TIMESTAMP") || typeName == wxT("TIME")) {
        type = new MySqlType(wxT("TIMESTAMP"), 0, IDbType::dbtTYPE_DATE_TIME);
    } else if(typeName == wxT("ENUM")) {
        type = new MySqlType(wxT("ENUM"), 0, IDbType::dbtTYPE_OTHER);
    } else if(typeName == wxT("SET")) {
        type = new MySqlType(wxT("SET"), 0, IDbType::dbtTYPE_OTHER);
    } else if(typeName == wxT("LONGBLOB")) {
        type = new MySqlType(wxT("LONGBLOB"), 0, IDbType::dbtTYPE_OTHER);
    } else if(typeName == wxT("BLOB")) {
        type = new MySqlType(wxT("BLOB"), 0, IDbType::dbtTYPE_OTHER);
    } else if(typeName == wxT("MEDIUMTEXT")) {
        type = new MySqlType(wxT("MEDIUMTEXT"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_TEXT);
    } else if(typeName == wxT("TEXT")) {
        type = new MySqlType(wxT("TEXT"), 0, IDbType::dbtTYPE_TEXT);
    } else if(typeName == wxT("LONGTEXT")) {
        type = new MySqlType(wxT("LONGTEXT"), 0, IDbType::dbtTYPE_TEXT);
    }
    // wxASSERT(type);
    return type;
}

wxArrayString* MySqlDbAdapter::GetDbTypes()
{
    return new wxArrayString(StdToWX::ToArrayString(
        { wxT("INT"), wxT("SMALLINT"), wxT("BIGINT"), wxT("TINYINT"), wxT("VARCHAR"), wxT("DOUBLE"), wxT("FLOAT"),
          wxT("DECIMAL"), wxT("BOOL"), wxT("DATETIME"), wxT("CHAR"), wxT("TIMESTAMP"), wxT("ENUM"), wxT("SET"),
          wxT("LONGBLOB"), wxT("BLOB"), wxT("MEDIUMTEXT"), wxT("TEXT"), wxT("LONGTEXT") }));
}
wxString MySqlDbAdapter::GetDefaultSelect(const wxString& dbName, const wxString& tableName)
{
    // TODO:SQL:
    wxString ret = wxString::Format(wxT("SELECT * FROM `%s`.`%s`"), dbName.c_str(), tableName.c_str());
    return ret;
}
wxString MySqlDbAdapter::GetDefaultSelect(const wxString& cols, const wxString& dbName, const wxString& tableName)
{
    // TODO:SQL:
    wxString ret = wxString::Format(wxT("SELECT %s FROM `%s`.`%s`"), cols.c_str(), dbName.c_str(), tableName.c_str());
    return ret;
}
bool MySqlDbAdapter::GetColumns(Table* pTab)
{
    DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT(""));

    if(!dbLayer->IsOpen())
        return false;
    // loading columns
    // TODO:SQL:
    DatabaseResultSet* database = dbLayer->RunQueryWithResults(
        wxString::Format(wxT("SHOW COLUMNS IN `%s`.`%s`"), pTab->GetParentName().c_str(), pTab->GetName().c_str()));
    while(database->Next()) {
        IDbType* pType = parseTypeString(database->GetResultString(2));
        if(pType) {
            Column* pCol = new Column(database->GetResultString(1), pTab->GetName(), pType);
            pTab->AddChild(pCol);
        }
    }
    dbLayer->CloseResultSet(database);

    // TODO:SQL:
    wxString constrSql =
        wxT("SELECT K.CONSTRAINT_SCHEMA, "
            "K.CONSTRAINT_NAME,K.TABLE_NAME,K.COLUMN_NAME,K.REFERENCED_TABLE_NAME,K.REFERENCED_COLUMN_NAME,R.UPDATE_"
            "RULE, R.DELETE_RULE FROM information_schema.KEY_COLUMN_USAGE K LEFT JOIN "
            "information_schema.REFERENTIAL_CONSTRAINTS R ON R.CONSTRAINT_NAME = K.CONSTRAINT_NAME AND "
            "K.CONSTRAINT_SCHEMA = R.CONSTRAINT_SCHEMA WHERE K.CONSTRAINT_SCHEMA = '%s' AND K.TABLE_NAME = '%s'");
    database = dbLayer->RunQueryWithResults(
        wxString::Format(constrSql, pTab->GetParentName().c_str(), pTab->GetName().c_str()));
    while(database->Next()) {
        Constraint* constr = new Constraint();
        constr->SetName(database->GetResultString(wxT("CONSTRAINT_NAME")));
        constr->SetLocalColumn(database->GetResultString(wxT("COLUMN_NAME")));
        constr->SetType(Constraint::primaryKey);
        if(database->GetResultString(wxT("REFERENCED_TABLE_NAME")) != wxT("")) {
            constr->SetType(Constraint::foreignKey);
            constr->SetRefTable(database->GetResultString(wxT("REFERENCED_TABLE_NAME")));
            constr->SetRefCol(database->GetResultString(wxT("REFERENCED_COLUMN_NAME")));

            wxString onDelete = database->GetResultString(wxT("UPDATE_RULE"));
            if(onDelete == wxT("RESTRICT"))
                constr->SetOnUpdate(Constraint::restrict);
            if(onDelete == wxT("CASCADE"))
                constr->SetOnUpdate(Constraint::cascade);
            if(onDelete == wxT("SET NULL"))
                constr->SetOnUpdate(Constraint::setNull);
            if(onDelete == wxT("NO ACTION"))
                constr->SetOnUpdate(Constraint::noAction);

            wxString onUpdate = database->GetResultString(wxT("DELETE_RULE"));
            if(onUpdate == wxT("RESTRICT"))
                constr->SetOnDelete(Constraint::restrict);
            if(onUpdate == wxT("CASCADE"))
                constr->SetOnDelete(Constraint::cascade);
            if(onUpdate == wxT("SET NULL"))
                constr->SetOnDelete(Constraint::setNull);
            if(onUpdate == wxT("NO ACTION"))
                constr->SetOnDelete(Constraint::noAction);
        }
        pTab->AddChild(constr);
    }
    dbLayer->CloseResultSet(database);
    dbLayer->Close();
    return true;
}

IDbType* MySqlDbAdapter::parseTypeString(const wxString& typeString)
{
    static wxRegEx reType(wxT("([a-zA-Z]+)(\\([0-9]+\\))?"));
    IDbType* type(NULL);
    if(reType.Matches(typeString)) {
        wxString typeName = reType.GetMatch(typeString, 1);
        wxString strSize = reType.GetMatch(typeString, 2);
        typeName.MakeUpper();

        type = this->GetDbTypeByName(typeName);
        if(type) {
            strSize.Trim().Trim(false);
            if(strSize.StartsWith(wxT("("))) {
                strSize.Remove(0, 1);
            }
            if(strSize.EndsWith(wxT(")"))) {
                strSize.RemoveLast();
            }

            long size = 0;
            if(strSize.ToLong(&size)) {
                type->SetSize(size);
            }
        }
    }
    return type;
}

bool MySqlDbAdapter::CanConnect() { return ((m_serverName != wxT("")) && (m_userName != wxT(""))); }
void MySqlDbAdapter::GetDatabases(DbConnection* dbCon)
{
    if(dbCon) {
        DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT(""));
        if(dbLayer) {
            if(!dbLayer->IsOpen())
                return;

            // loading databases
            // TODO:SQL:
            DatabaseResultSet* databaze = dbLayer->RunQueryWithResults(wxT("SHOW DATABASES"));
            while(databaze->Next()) {
                dbCon->AddChild(new Database(this, databaze->GetResultString(1)));
            }
            dbLayer->CloseResultSet(databaze);
            dbLayer->Close();
        }
    }
    return;
}

void MySqlDbAdapter::GetTables(Database* db, bool includeViews)
{
    if(db) {
        DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT(""));
        if(dbLayer) {
            if(!dbLayer->IsOpen())
                return;
            // lading tables for database
            // TODO:SQL:

            // DatabaseResultSet *tabulky = dbLayer->RunQueryWithResults(wxString::Format(wxT("SHOW TABLES IN `%s`"),
            // db->getName().c_str()) );

            DatabaseResultSet* tabulky = NULL;
            if(!includeViews) {
                tabulky = dbLayer->RunQueryWithResults(
                    wxString::Format(wxT("SELECT * FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = '%s' AND "
                                         "`TABLE_TYPE` = 'BASE TABLE'"),
                                     db->GetName().c_str()));
            } else {
                tabulky = dbLayer->RunQueryWithResults(
                    wxString::Format(wxT("SELECT * FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = '%s' AND "
                                         "(`TABLE_TYPE` = 'BASE TABLE' OR `TABLE_TYPE` = 'VIEW')"),
                                     db->GetName().c_str()));
            }
            if(tabulky) {
                while(tabulky->Next()) {
                    db->AddChild(new Table(this, tabulky->GetResultString(wxT("TABLE_NAME")), db->GetName(),
                                           tabulky->GetResultString(wxT("TABLE_TYPE")).Contains(wxT("VIEW"))));
                }
                dbLayer->CloseResultSet(tabulky);
            }
            dbLayer->Close();
        }
    }
    return;
}
wxString MySqlDbAdapter::GetCreateDatabaseSql(const wxString& dbName)
{
    return wxString::Format(wxT("CREATE DATABASE `%s`"), dbName.c_str());
}
wxString MySqlDbAdapter::GetDropTableSql(Table* pTab)
{
    return wxString::Format(wxT("SET FOREIGN_KEY_CHECKS = 0;\nDROP TABLE IF EXISTS `%s`;\nSET FOREIGN_KEY_CHECKS = 1;"),
                            pTab->GetName().c_str());
}
wxString MySqlDbAdapter::GetAlterTableConstraintSql(Table* tab)
{
    // TODO:SQL:
    wxString str = wxString::Format(wxT("-- ---------- CONSTRAINTS FOR TABLE `%s` \n"), tab->GetName().c_str());
    str.append(wxT("-- -------------------------------------------------------------\n"));
    wxString prefix = wxString::Format(wxT("ALTER TABLE `%s` "), tab->GetName().c_str());

    SerializableList::compatibility_iterator node = tab->GetFirstChildNode();
    while(node) {
        Constraint* constr = NULL;
        constr = wxDynamicCast(node->GetData(), Constraint);
        if(constr) {
            if(constr->GetType() == Constraint::foreignKey) {
                str.append(prefix +
                           wxString::Format(wxT("ADD CONSTRAINT `%s` FOREIGN KEY (`%s`) REFERENCES `%s`(`%s`) "),
                                            constr->GetName().c_str(), constr->GetLocalColumn().c_str(),
                                            constr->GetRefTable().c_str(), constr->GetRefCol().c_str()));
                str.append(wxT("ON UPDATE "));
                switch(constr->GetOnUpdate()) {
                case Constraint::restrict:
                    str.append(wxT("RESTRICT "));
                    break;
                case Constraint::cascade:
                    str.append(wxT("CASCADE "));
                    break;
                case Constraint::setNull:
                    str.append(wxT("SET NULL "));
                    break;
                case Constraint::noAction:
                    str.append(wxT("NO ACTION "));
                    break;
                }
                str.append(wxT("ON DELETE "));
                switch(constr->GetOnDelete()) {
                case Constraint::restrict:
                    str.append(wxT("RESTRICT "));
                    break;
                case Constraint::cascade:
                    str.append(wxT("CASCADE "));
                    break;
                case Constraint::setNull:
                    str.append(wxT("SET NULL "));
                    break;
                case Constraint::noAction:
                    str.append(wxT("NO ACTION "));
                    break;
                }
                str.append(wxT("; \n"));
            }
        } // if (constr->GetType() == Constraint::primaryKey) str.append(prefix + wxString::Format(wxT("ADD CONSTRAINT
          // `%s` PRIMARY KEY (`%s`); \n"), constr->GetName().c_str(), constr->GetLocalColumn().c_str()));

        node = node->GetNext();
    }
    str.append(wxT("-- -------------------------------------------------------------\n"));
    return str;
}
wxString MySqlDbAdapter::GetDropDatabaseSql(Database* pDb)
{
    return wxString::Format(wxT("DROP DATABASE `%s`"), pDb->GetName().c_str());
}
wxString MySqlDbAdapter::GetUseDb(const wxString& dbName) { return wxString::Format(wxT("USE `%s`"), dbName.c_str()); }
void MySqlDbAdapter::GetViews(Database* db)
{
    DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT(""));

    if(!dbLayer->IsOpen())
        return;
    // loading columns
    // TODO:SQL:
    DatabaseResultSet* database = dbLayer->RunQueryWithResults(wxString::Format(
        wxT("SELECT * FROM `INFORMATION_SCHEMA`.`VIEWS` WHERE TABLE_SCHEMA = '%s'"), db->GetName().c_str()));
    while(database->Next()) {
        View* pView = new View(this, database->GetResultString(wxT("TABLE_NAME")), db->GetName(),
                               database->GetResultString(wxT("VIEW_DEFINITION")));
        db->AddChild(pView);
    }
    dbLayer->CloseResultSet(database);
}

wxString MySqlDbAdapter::GetCreateViewSql(View* view, bool dropView)
{
    wxString str = wxT("");
    if(view) {
        if(dropView) {
            str.append(wxString::Format(wxT("DROP VIEW IF EXISTS `%s`;\n"), view->GetName().c_str()));
        }
        str.append(
            wxString::Format(wxT("CREATE VIEW `%s` AS %s ;\n"), view->GetName().c_str(), view->GetSelect().c_str()));
    }
    str.append(wxT("-- -------------------------------------------------------------\n"));
    return str;
}
void MySqlDbAdapter::ConvertTable(Table* pTab)
{
    SerializableList::compatibility_iterator node = pTab->GetFirstChildNode();
    while(node) {
        if(node->GetData()->IsKindOf(CLASSINFO(Column))) {
            Column* col = (Column*)node->GetData();
            col->SetType(ConvertType(col->GetType()));
        }
        node = node->GetNext();
    }
}

IDbType* MySqlDbAdapter::ConvertType(IDbType* pType)
{
    IDbType* newType = wxDynamicCast(pType, MySqlType);
    if(!newType) {
        newType = GetDbTypeByUniversalName(pType->GetUniversalType());
        delete pType;
    }
    return newType;
}

IDbType* MySqlDbAdapter::GetDbTypeByUniversalName(IDbType::UNIVERSAL_TYPE type)
{
    IDbType* newType = NULL;
    switch(type) {
    case IDbType::dbtTYPE_INT:
        newType = GetDbTypeByName(wxT("INT"));
        break;
    case IDbType::dbtTYPE_BOOLEAN:
        newType = GetDbTypeByName(wxT("BOOL"));
        break;
    case IDbType::dbtTYPE_DATE_TIME:
        newType = GetDbTypeByName(wxT("DATETIME"));
        break;
    case IDbType::dbtTYPE_DECIMAL:
        newType = GetDbTypeByName(wxT("DECIMAL"));
        break;
    case IDbType::dbtTYPE_FLOAT:
        newType = GetDbTypeByName(wxT("FLOAT"));
        break;
    case IDbType::dbtTYPE_TEXT:
        newType = GetDbTypeByName(wxT("TEXT"));
        break;
    case IDbType::dbtTYPE_OTHER:
        newType = GetDbTypeByName(wxT("BLOB"));
        break;
    }
    return newType;
}
IDbAdapter* MySqlDbAdapter::Clone() { return new MySqlDbAdapter(m_serverName, m_userName, m_password); }

wxString MySqlDbAdapter::GetDropViewSql(View* pView)
{
    return wxString::Format(wxT("DROP VIEW IF EXISTS `%s`;"), pView->GetName().c_str());
}
